package cn.senjoeson.demo.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import cn.senjoeson.demo.bean.User;


public class UserDao {

    private final static String driver = "com.mysql.jdbc.Driver";
    private final static String url = "jdbc:mysql://127.0.0.1:3306/users";
    private final static String username = "root";
    private final static String password = "root";
    private static Connection connection = null;
    private static PreparedStatement ps = null;

    public Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        connection = DriverManager.getConnection(url, username, password);
        return connection;
    }

    public void closeConnection() {
        try {
            if (connection != null)
                connection.close();
            if (ps != null)
                ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 保存一个用户信息(注册)
     *
     * @param user
     * @throws SQLException
     */
    public void saveUser(User user) throws SQLException {
        try {
            connection = getConnection();
            String sql = "insert into user (userName, password) values(?,?)";
            ps = connection.prepareStatement(sql);
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection();
        }
    }

    /**
     * 根据username 查找是否存在用户名
     *
     * @param username
     * @return
     * @throws SQLException
     */
    public User findByName(String username) {
        ResultSet rs = null;
        User user = new User();
        try {
            connection = getConnection();
            System.out.println("开始读取数据");
            String sql = "select * from user where userName='" + username + "'";
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString(2) + "\t" + rs.getString(3));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                return user;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection();
        }
        return user;
    }

    public void deleteUser(String name) throws ClassNotFoundException, SQLException {
        Connection connection = getConnection();
        String sql = "delete from user where userName='" + name + "'";
        ps = connection.prepareStatement(sql);
        ps.execute(sql);
        closeConnection();
    }

    public void changUser(User user) throws SQLException, ClassNotFoundException {
        Connection connection = getConnection();
        String sql = "update user set password='" + user.getPassword() + "' where userName='" + user.getUsername() + "' ";
        ps = connection.prepareStatement(sql);
        ps.execute(sql);
        closeConnection();
    }

}  